#
# Regarding structure of table:
#
# We are mostly interested in trips where security >= some lower limit.
# So, "security" field is indexed as first field of PK, but not as individual index.
#
# Few ids and names (as persistent as "length" and "security" are) were denormalized
# into "trips": for direct use in selects from "market" instead of making new joins there.
#

__all__ = ['Trips']
from ..storage import *
class Trips(Storage):

    SQL_DROP = ["""
        DROP TABLE IF EXISTS "trips"
    """]

    SQL_CREATE = ["""
        CREATE TABLE "trips" (
            "source_system_id"      integer         not null, -- REFERENCES "systems" MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
            "target_system_id"      integer         not null, -- REFERENCES "systems" MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
            "security"              smallint        not null,
            "length"                smallint        not null
        ) WITHOUT OIDS """,
#        """ CREATE INDEX "trips_source_system_id" ON "trips" ("source_system_id") """,#???
#        """ CREATE INDEX "trips_target_system_id" ON "trips" ("target_system_id") """,#???
#        """ CREATE INDEX "trips_source_to_target" ON "trips" ("source_system_id", "target_system_id") """,#???
#        """ CREATE INDEX "trips_target_to_source" ON "trips" ("target_system_id", "source_system_id") """,#???
        """ CREATE UNIQUE INDEX "trips_source_to_target_w_security" ON "trips" ("source_system_id", "target_system_id", "security") """,# used in market analyzing (shortest route with security level) !!!UNIQUE!!!PK!!!
#        """ CREATE INDEX "trips_length_and_target" ON "trips" ("length", "target_system_id") """,# used in joins while augmenting (see below)
#        """ CREATE UNIQUE INDEX "trips_target_to_source_w_security" ON "trips" ("target_system_id", "source_system_id", "security") """,# used in joins while augmenting (see below)
#        """ CREATE INDEX "trips_security"         ON "trips" ("security") """,#??? market-find.py?
        """ CREATE INDEX "trips_length"           ON "trips" ("length") """,# used in step-by-step augmenting below
    ]

    SQL_PREFILL = ["""
        INSERT INTO "trips" (
            "source_system_id",
            "target_system_id",
            "security",
            "length"
        )
        SELECT
            s."system_id",
            s."system_id",
            s."security",
            0 -- ??? ставить 1 в таких случаях? чтоб не делать в маркете greatest(1,t.length)
        FROM "systems" s
    """]

    SQL_AUGMENT = ["""
        INSERT INTO "trips" (
            "source_system_id",
            "target_system_id",
            "security",
            "length"
        )
        SELECT
            t."source_system_id",
            j."target_system_id",
            least(t."security", j."security"), -- NEVER use something instead of t.security, because t.security is the least of all securities on the way!
            %(length)s + 1
        FROM "trips" t
        JOIN "jumps" j on (t."target_system_id" = j."source_system_id")
        WHERE t."length" = %(length)s
          AND NOT EXISTS
            (
                SELECT * FROM "trips" e
                WHERE (e."security" >= t."security" OR e."security" >= j."security")
                  AND e."source_system_id" = t."source_system_id"
                  AND e."target_system_id" = j."target_system_id"
            )
        GROUP BY
            t."source_system_id",
            j."target_system_id",
            least(t."security", j."security")
    """]

    SQL_ANALYZE = ["""
        ANALYZE "trips"
    """]

    SQL_STATS = """
        SELECT count(*) as "count", max("length") as "max_length", min("length") as "min_length"
        FROM "trips"
    """

    def drop(self):
        self.execute(self.SQL_DROP)

    def create(self):
        self.execute(self.SQL_CREATE)

    def prefill(self):
        self.execute(self.SQL_PREFILL)

    def augmentFrom(self, length):
        self.execute(self.SQL_AUGMENT, [{"length": length}])

    def augmentTo(self, length):
        self.execute(self.SQL_AUGMENT, [{"length": length - 1}])

    def analyze(self):
        self.execute(self.SQL_ANALYZE)

    def stats(self):
        return self.query(self.SQL_STATS).fetchall()[0]
